package com.wyq.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.wyq.bean.Audience_Q;
import com.wyq.bean.Audience_Qtitle;
import com.wyq.dao.AudienceQDao;
import com.wyq.util.StringUtil;
import com.wyq.util.impl.JDBCDAOSupportImpl;

public class AudienceQDaoImpl extends JDBCDAOSupportImpl implements AudienceQDao {

	//删除问卷调查内容
	public void removeAudienceQContent(Audience_Q aqc) throws Exception {
		this.getHibernateTemplate().delete(aqc);
	}

	//删除问卷调查标题
	public void removeAudienceQtitle(Audience_Qtitle aq) throws Exception {
		this.getHibernateTemplate().delete(aq);
		//删除标题同时删除内容
		String delContentSql = "delete from audience_q where Qtitle_id = "+aq.getId();
		this.execupdateBySql(delContentSql);
	}

	//保存问卷调查标题
	public int saveAudienceQtitle(Map qTitlemap,final int flag) throws Exception {
		//flag为0时，新增，为1时修改
		final String insertSql = "insert into audience_qtitle(Qtitle,isCheckBox,isAvalible,show_id) values('"
						+ qTitlemap.get("Qtitle") + "',"
						+ qTitlemap.get("isCheckBox") + ","
						+ qTitlemap.get("isAvalible") + ","
						+ qTitlemap.get("show_id") + ")";
		final String updateSql = "update audience_qtitle set Qtitle = '" + qTitlemap.get("Qtitle") 
						+ "',isCheckBox = " + qTitlemap.get("isCheckBox") 
						+ ",isAvalible = " + qTitlemap.get("isAvalible") 
						+ ",show_id = '" + qTitlemap.get("show_id") +"'"
						+ " where id = " + qTitlemap.get("id");
		this.getHibernateTemplate().setAllowCreate(true);
		return (Integer)this.getHibernateTemplate().execute(new HibernateCallback() {
			
			public Object doInHibernate(Session arg0) throws HibernateException,
					SQLException {
				int result =-1;
				try{
					SQLQuery sqlQuery = null;
					if(flag == 0){
						sqlQuery = arg0.createSQLQuery(insertSql);
					}else if(flag == 1){
						sqlQuery = arg0.createSQLQuery(updateSql);
					}
					result = sqlQuery.executeUpdate();
					return new Integer(result);
				}catch (Exception e) {
					
					throw new RuntimeException(e.getMessage(),e);
					
				}
			}});
		
	}

	//保存问卷调查标题
	public int saveAudienceQContent(Map qmap,final int flag) throws Exception {
		//flag为0时，新增，为1时修改
		final String insertSql = "insert into audience_q (Qtext,isAvalible,Qtitle_id) values('"
						+ qmap.get("Qtext") + "',"
						+ qmap.get("isAvalible") + ","
						+ qmap.get("Qtitle_id") + ")";
		final String updateSql = "update audience_q set Qtext = '"
			+ qmap.get("Qtext") + "',isAvalible = "
			+ qmap.get("isAvalible") + ",Qtitle_id = "
			+ qmap.get("Qtitle_id") + " where id = "
			+ qmap.get("id");
		this.getHibernateTemplate().setAllowCreate(true);
		return (Integer)this.getHibernateTemplate().execute(new HibernateCallback() {
			
			public Object doInHibernate(Session arg0) throws HibernateException,
					SQLException {
				int result =-1;
				try{
					SQLQuery sqlQuery = null;
					if(flag == 0){
						sqlQuery = arg0.createSQLQuery(insertSql);
					}else if(flag == 1){
						sqlQuery = arg0.createSQLQuery(updateSql);
					}
					result = sqlQuery.executeUpdate();
					return new Integer(result);
				}catch (Exception e) {
					
					throw new RuntimeException(e.getMessage(),e);
					
				}
			}});
		
		
	}
	
	public Set<Audience_Q> findAllAudience_Q(Audience_Qtitle aq) throws Exception {
		return aq.getAudienceQs();
		//return null;
	}
	
	/*public List<Audience_Q> findAllAudience_Q() throws Exception {
		String sql = "select * from audience_Q where isAvalible=1";
		List<Audience_Q> list = new ArrayList<Audience_Q>();
		list = this.getListMapBySql(sql);
		return list;
	}*/

	/**
	 * 获取观众注册调查问卷信息
	 */
	@SuppressWarnings("unchecked")
	public List<Audience_Qtitle> findAllAudience_Qtitle(String show_id) throws Exception {
		//String sql="select * from audience_Qtitle where isAvalible=1";
		List<Audience_Qtitle> list = new ArrayList<Audience_Qtitle>();
		//list = this.getHibernateTemplate().
		//list = this.getListMapBySql(sql);
		//String hql="from com.wyq.bean.Audience_Qtitle where isAvalible=1 and type="+type;
		String hql="from com.wyq.bean.Audience_Qtitle  where isAvalible=1";
		if(StringUtil.isNotEmpty(show_id)){
			hql += " and show_id='"+show_id+"'";
		}
		
		list = this.getHibernateTemplate().find(hql);
		return list;
	}

	//根据ID获取调查问卷标题名称
	public String getQTitleName(String qTitleID) throws Exception {
		String qTitleName = "";
		
		String sql = "select qtitle from audience_qtitle where id = "+qTitleID;
		List<Map> list = this.getListMapBySql(sql);
		if(null != list && list.size() > 0){
			if(null != list.get(0).get("Qtitle")){
				qTitleName = list.get(0).get("Qtitle").toString();
			}
			//System.out.println("List:::" + list);
		}
		return qTitleName;
	}
	
	//根据ID获取调查问卷内容名称
	public String getQText(String qID) throws Exception{
		String qText = "";
		
		String sql = "select qtext from audience_q where id = "+qID;
		List<Map> list = this.getListMapBySql(sql);
		if(null != list && list.size() > 0){
			if(null != list.get(0).get("Qtext")){
				if(null != list.get(0).get("Qtext")){
					qText = list.get(0).get("Qtext").toString();
				}
			}
			//System.out.println("List:::" + list);
		}
		return qText;
	}
	
	//获取问卷调查标题
	public List findAllQTitle(final String showInfoId) throws Exception {
		
		this.getHibernateTemplate().setAllowCreate(true);
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {				
			public Object doInHibernate(Session arg0) throws HibernateException,
					SQLException {
				try{
					String sql = "select t.*,s.showName from audience_qtitle t left join show_info s on t.show_id = s.id where 1=1  ";
					if(StringUtil.isNotEmpty(showInfoId)){
						sql += " and t.show_id = '"+showInfoId+"'";
					}
										
					SQLQuery sqlQuery = arg0.createSQLQuery(sql);
					sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
					List result = sqlQuery.list();
					return result;
				}catch (Exception e) {
					throw new RuntimeException(e.getMessage(),e);
				}
			}
		});
	}
	
	//根据问卷调查标题获取内容
	public List findAllQContentByQTitleId(final String qTitleid) throws Exception{
		this.getHibernateTemplate().setAllowCreate(true);
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {				
			public Object doInHibernate(Session arg0) throws HibernateException,
					SQLException {
				try{
					String sql = "select q.*,t.Qtitle,t.show_id,s.showName from audience_q q left join audience_qtitle t on q.Qtitle_id = t.id"
							+" left join show_info s on t.show_id = s.id where 1=1";
					if(null == qTitleid || "".equals(qTitleid)){
						sql += " order by q.Qtitle_id";
					}else {
						sql += " and q.Qtitle_id = " + qTitleid + " order by q.Qtitle_id";
					}
										
					SQLQuery sqlQuery = arg0.createSQLQuery(sql);
					sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
					List result = sqlQuery.list();
					return result;
				}catch (Exception e) {
					throw new RuntimeException(e.getMessage(),e);
				}
			}
		});
	}

}
